package com.krm;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * 读取excel
 *
 * @author lgw
 *
 * @date 2019-07-22
 */
public class ReadExcel {

    /**
     * 得到Excel 标签名称
     * @param path
     * @return
     */
    public List<String> getExcelSheetName(String path){
        List<String> list = new ArrayList<>();
        try {
            InputStream is =  new FileInputStream(path);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            //读sheet
            for (int numSheet = 0; numSheet <xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                String sheetName = xssfSheet.getSheetName();
                list.add(sheetName);
            }
        } catch (Exception e) {
                e.printStackTrace();
            }
        return list;
    }

    /**
     *
     * 从第一行读取Excel
     * @param path 路径
     * @return
     */
    public  List<SQLInsterBean> readExcelForSqlInster(String path){
        List<SQLInsterBean> list = new ArrayList<SQLInsterBean>();
        try {
            InputStream is =  new FileInputStream(path);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

            //读sheet
            for (int numSheet = 0; numSheet <xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                String sheetName= xssfSheet.getSheetName();
                List<String> headlist = new ArrayList<String>();
                List<List<Object>> datalist = new ArrayList<List<Object>>();

                //读行
                for(int rowNum=0;rowNum<=xssfSheet.getLastRowNum();rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow==null) {
                        continue;
                    }
                    List<Object> celllist = new ArrayList<Object>();
                    for(int cellNum =0;cellNum<xssfRow.getLastCellNum();cellNum++){
                        if(rowNum==0){
                            headlist.add((String)getValue(xssfRow.getCell(cellNum)));
                        }else{
                            XSSFCell value = xssfRow.getCell(cellNum);
                            if(value==null){
                                celllist.add(null);
                            }else {
                                celllist.add(getValue(value));
                            }

                        }

                    }
                    datalist.add(celllist);
                }
                SQLInsterBean si = new SQLInsterBean();
                si.setSheetName(sheetName);
                si.setHeadlist(headlist);
                si.setDatalist(datalist);
                list.add(si);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;

    }

    /**
     *
     * 按模板读取Excel
     * @param path 路径
     * @return
     */
    public  List<ExcelBean> readExcel(String path){

        List<ExcelBean> list = new ArrayList<ExcelBean>();

        try {
            InputStream is =  new FileInputStream(path);
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

            //读sheet
            for (int numSheet = 0; numSheet <xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if(xssfSheet==null){
                    continue;
                }


                XSSFRow xssfRow1 = xssfSheet.getRow(0);
                if(xssfRow1==null){
                    continue;
                }
                XSSFCell XSSFCell1 = xssfRow1.getCell(1);
                if(XSSFCell1==null){
                    continue;
                }
                String tableCommt = XSSFCell1.getStringCellValue();

                ExcelBean excelBean = new ExcelBean();
                excelBean.setTableCommt(tableCommt);

                XSSFRow xssfRow2 = xssfSheet.getRow(1);
                if(xssfRow2==null){
                    continue;
                }
                XSSFCell XSSFCell2 = xssfRow2.getCell(1);
                if(XSSFCell2==null){
                    continue;
                }
                String tableName = XSSFCell2.getStringCellValue();
                excelBean.setTableName(tableName);

                List<TableBean> listTableBean = new ArrayList<TableBean>();
                //读行
                for(int rowNum=4;rowNum<=xssfSheet.getLastRowNum();rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow!=null){
                        TableBean table = new TableBean();
                        table.setCommt((String)getValue(xssfRow.getCell(1)));
                        table.setClunmeName((String)getValue(xssfRow.getCell(2)));
                        table.setType((String)getValue(xssfRow.getCell(3)));
                        table.setLength(getValue(xssfRow.getCell(4)).toString());
                        table.setRecision(String.valueOf(getValue(xssfRow.getCell(5))));
                        String ispkid = (String)getValue(xssfRow.getCell(6));
                        if("是".equals(ispkid.trim())){
                            table.setIsPkid(ispkid.trim());
                        }
                        listTableBean.add(table);
                    }
                }
                excelBean.setTableBean(listTableBean);
                list.add(excelBean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    private static Object getValue(XSSFCell xssfRow){
        if(xssfRow==null){
            return "";
        }
       if(xssfRow.getCellType()== CellType.NUMERIC){
          Double d =  xssfRow.getNumericCellValue();
//          return String.valueOf(d.intValue());
           return d.intValue();
        }else{
//            return String.valueOf(xssfRow.getStringCellValue());
           return xssfRow.getStringCellValue();
        }

    }
}
